﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VideoMonitorSystem.Model;
using System.Data;
using System.Data.SqlClient;

namespace VideoMonitorSystem.DAL
{
    /// <summary>
    /// 系统类型数据层处理类
    /// </summary>
    public class SystemTypeService
    {


        /// <summary>
        /// 依据SQL语句查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>满足SQL语句的结果集</returns>
        private static List<SystemType> GetSystemTypeBySQL(string sql)
        {
            List<SystemType> list = new List<SystemType>();
            using(DataTable dataTable = DBHelper.GetDataTable(sql))
            {
                foreach(DataRow row in dataTable.Rows)
                {
                    SystemType systemType = new SystemType();
                    systemType.StID = Convert.ToInt32(row["sID"]);
                    systemType.StName = (string)row["stName"];
                    systemType.SDesc = (string)row["sDesc"];
                    systemType.SFilePath = (string)row["sFilePath"];
                    systemType.SInTime = (DateTime)row["sInTime"];
                    list.Add(systemType);
                }
                return list;
            }
           
        }

        /// <summary>
        /// 获取所有的系统类型
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>满足SQL语句的结果集</returns>
        public static List<SystemType> GetSystemTypeAll()
        {
            string sql = "SELECT * FROM systemType ORDER BY sID ASC";
            return GetSystemTypeBySQL(sql);
        }

        /// <summary>
        /// 获取指定的ID的系统类型
        /// </summary>
        /// <param name="id">系统类型ID</param>
        /// <returns>指定ID系统类型的对象</returns>
        public static SystemType GetSystemTypeByID(int id)
        {
            string sql = "SELECT * FROM systemType WHERE sID = @stID";
            using (DataTable dataTable = DBHelper.GetDataTable(sql, new SqlParameter("@stID", id))) 
            {
                if (dataTable.Rows.Count > 0)
                {
                    SystemType systemType = new SystemType();
                    systemType.StID = Convert.ToInt32(dataTable.Rows[0]["sID"].ToString());
                    systemType.StName = (string)dataTable.Rows[0]["stName"];
                    systemType.SDesc = (string)dataTable.Rows[0]["sDesc"];
                    systemType.SFilePath = (string)dataTable.Rows[0]["sFilePath"];
                    systemType.SInTime = (DateTime)dataTable.Rows[0]["sInTime"];

                    return systemType;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 新增一个系统类型
        /// </summary>
        /// <param name="systemType">系统类型对象</param>
        /// <returns>受影响的行数</returns>
        public static int AddSystemType(SystemType systemType)
        {
            string sql = "INSERT INTO systemType(stName,sDesc,sFilePath) VALUES(@stName,@sDesc,@sFilePath)";
            SqlParameter[] paremeter = new SqlParameter[] { 
                    new SqlParameter("@stName",systemType.StName),
                    new SqlParameter("@sDesc",systemType.SDesc),
                    new SqlParameter("@sFilePath",systemType.SFilePath)
            };
            return DBHelper.GetScalar(sql, paremeter);
        }
        /// <summary>
        /// 更新指定系统类型的信息
        /// </summary>
        /// <param name="systemType">被更新的对象</param>
        /// <returns>受影响的行数</returns>
        public static int ModifySystemInfo(SystemType systemType)
        {
            string sql = "UPDATE systeminfo SET stName = @stName,sDesc = @sDesc,sFilePath = @sFilePath WHERE stID = @stID";
            SqlParameter[] paremeter = new SqlParameter[] { 
                    new SqlParameter("@stName",systemType.StName),
                    new SqlParameter("@sDesc",systemType.SDesc),
                    new SqlParameter("@sFilePath",systemType.SFilePath),
                    new SqlParameter("@stID",systemType.StID)
            };
            return DBHelper.GetScalar(sql, paremeter);
        }
        

    
    }
}
